﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text.RegularExpressions;

/*********************************************************
 * 
 * programmer:		patrick.woolhoo@gmail.com
 * svn:				http://woolhoo-net.googlecode.com/svn/trunk/wnet/
 * class:			Model
 * desc:			数据模型类，实现便捷数据库操作
 * 
 * *******************************************************/

namespace Patrick.Custom {
	public class Model {
		/// <summary>
		/// 系统所有的错误状态
		/// </summary>
		public enum Errors {
			ERR_OK,
			ERR_DB_ERROR,
			ERR_UPLOAD,
			ERR_EMPTY_INPUT,
			ERR_UPLOAD_FILE_TYPE_NOT_ALLOWED, 
			ERR_UNEXPECTED, 
			ERR_ON_DELETION, 
			ERR_TABLE_NOT_FOUND,
			ERR_UPLOAD_NOT_ALLOWED_TYPE
		};





		#region 私有变量


		/// <summary>
		/// 所有错误信息
		/// </summary>
		private string[] ErrorMsgs = {
			"OK", 
			"DB Operation failed", 
			"Upload failed", 
			"No data posted", 
			"Upload file type not allowed", 
			"Unexpected error", 
			"Deleting file failed", 
			"Table not found"
		};

		/// <summary>
		/// 受影响的行数
		/// </summary>
		private int _Affected = 0;

		/// <summary>
		/// 系统当前的错误代码
		/// </summary>
		private Errors _ErrNo = 0;

		/// <summary>
		/// 当前实例所涉及的表的名称，带前缀
		/// </summary>
		private string _TableName = null;

		/// <summary>
		/// 当前实例所涉及的表的名称，不带前缀，和表单内的元素名称前缀一致
		/// </summary>
		private string _ShortTableName = null;

		/// <summary>
		/// 表的前缀
		/// </summary>
		private string _Prefix = null;

		/// <summary>
		/// 最后执行的sql语句
		/// </summary>
		private StringBuilder _Sql = new StringBuilder();

		/// <summary>
		/// 更新操作涉及到的id
		/// </summary>
		private int _Id = 0;

		/// <summary>
		/// 多记录操作涉及到的id
		/// </summary>
		private List<int> _Ids = new List<int>();

		/// <summary>
		/// 用户提交的所有内容，如果有文件被上传还包含文件上传后的访问位置
		/// </summary>
		private NameValueCollection _Data = new NameValueCollection();

		/// <summary>
		/// 如果有文件被上传则包含文件上传后的访问位置
		/// </summary>
		private NameValueCollection _Uploaded = new NameValueCollection();

		/// <summary>
		/// 数据库连接字符串
		/// </summary>
		private string _ConnStr = null;

		/// <summary>
		/// 数据库连接
		/// </summary>
		private SqlConnection _Conn = null;

		/// <summary>
		/// 数据库事务
		/// </summary>
		private SqlTransaction _Trans = null;

		/// <summary>
		/// 指示连接对象是否对象自己建立并打开的，如果是那么要负责关闭
		/// </summary>
		private bool _OpenedInside = false;

		#endregion


		#region 属性

		/// <summary>
		/// 受影响的行数
		/// </summary>
		public int Affected {
			get { return this._Affected; }
		}

		/// <summary>
		/// 错误代码
		/// </summary>
		public Errors ErrNo {
			get { return _ErrNo; }
		}

		/// <summary>
		/// 系统当前的错误信息
		/// </summary>
		public string ErrorMsg {
			get { return ErrorMsgs[(int)this._ErrNo]; }
		}

		/// <summary>
		/// 当前实例所涉及的表的名称，带前缀
		/// </summary>
		public string TableName {
			get { return _TableName; }
		}

		/// <summary>
		/// 当前实例所涉及的表的名称，不带前缀，与表单元素名称前缀一致
		/// </summary>
		public string ShortTableName {
			get { return _ShortTableName; }
		}

		/// <summary>
		/// 当前实例所涉及的表的名称，不带前缀，与表单元素名称前缀一致
		/// </summary>
		public string Prefix {
			get { return this._Prefix; }
		}

		/// <summary>
		/// 最后执行的sql语句
		/// </summary>
		public string Sql {
			get { return _Sql.ToString(); }
		}

		/// <summary>
		/// 提交操作后涉及到的id
		/// </summary>
		public int Id {
			get { return _Id; }
		}

		/// <summary>
		/// 多记录操作涉及到的id
		/// </summary>
		public List<int> Ids {
			get { return _Ids; }
		}

		/// <summary>
		/// 用户提交的所有内容，如果有文件被上传还包含文件上传后的访问位置
		/// </summary>
		public NameValueCollection Data {
			get { return this._Data; }
		}

		/// <summary>
		/// 用户提交的文件上传后的访问位置
		/// </summary>
		public NameValueCollection Uploaded {
			get { return this._Uploaded; }
		}

		/// <summary>
		/// 当前实例使用的ConnStr
		/// </summary>
		public string ConnStr {
			get { return this._ConnStr; }
		}


		public SqlTransaction Trans {
			get {return this._Trans;}
		}

		#endregion


		#region 静态方法

		/// <summary>
		/// 取得 Web.Config 中可能定义的 AppSettings["TablePrefix"]
		/// 暂时没启用
		/// </summary>
		/// <returns></returns>
		public static string TablePrefix() {
			return ConfigurationManager.AppSettings["TablePrefix"];
		}


		/// <summary>
		/// 取得 Web.Config 中可能定义的 ConnectionString
		/// </summary>
		/// <returns></returns>
		public static string GetConnStrInConfig() {
			return ConfigurationManager.ConnectionStrings[0].ConnectionString;
		}

		public static string GetConnStrInConfig(int i) {
			return ConfigurationManager.ConnectionStrings[i].ConnectionString;
		}



		/// <summary>
		/// 将 news.id 之类的名称格式化为 [news].[id]
		/// </summary>
		/// <param name="names"></param>
		/// <returns></returns>
		public static string FormatNames(string names) {
			if (names.Trim().Length == 0) return "";

			string[] temp = names.Split(new char[] { '.' });
			StringBuilder ret = new StringBuilder();
			foreach (string s in temp) {
				if (s.Trim().Length == 0) continue;
				else if (s.Trim() == "*") ret.Append("*");
				else ret.Append("[" + s.Trim() + "]");
			}
			return ret.ToString();
		}




		/// <summary>
		/// 根据数据类型返回适合插入数据库的字符串（是否用单引号括起来）
		/// </summary>
		/// <param name="DataType"></param>
		/// <param name="Value"></param>
		/// <returns></returns>
		public static string GetProperValue(int DataType, string Value) {
			switch (DataType) {
				case -5:		//bigint
				case 4:			//int
				case 5:			//smallint
				case -6:		//tinyint
				case -7:		//bit
				case 3:			//decimal, money, smallmoney
				case 2:			//numeric
				case 6:			//float
				case 7:			//real
					return Value;
				/*
				case 11:		//datetime, smalldatetime
				case 1:			//char
				case 12:		//varchar
				case -1:		//text
				case -8:		//nchar
				case -9:		//nvarchar
				case -10:		//ntext
				case -2:		//binary
				case -3:		//varbinary
				case -4:		//image
				*/
				default:
					return "'" + addQuote(Value) + "'";
			}
		}





		/// <summary>
		/// 返回指定的数据类型是否为字符串型或日期型(需要单引号括起来的)，如果返回false则一定是数字型
		/// </summary>
		/// <param name="DateType"></param>
		/// <returns></returns>
		public static bool IsStringType(int DataType) {
			switch (DataType) {
				case -5:		//bigint
				case 4:			//int
				case 5:			//smallint
				case -6:		//tinyint
				case -7:		//bit
				case 3:			//decimal, money, smallmoney
				case 2:			//numeric
				case 6:			//float
				case 7:			//real
					return false;
				default:
					return true;
			}
		}


		public static string addQuote(string str) {
			return str.Replace("'", "''");
		}


		#endregion


		#region 方法

		#region helper

		/// <summary>
		/// 设置错误状态
		/// </summary>
		/// <param name="ErrorNo"></param>
		private void SetError(Errors ErrorNo) {
			this._ErrNo = ErrorNo;
		}

		/// <summary>
		/// 打开连接，当此连接在构造函数中定义或在构造函数中作为参数传递进来，事物的不负责打开
		/// </summary>
		private void OpenConnection() {
			if (this._Conn.State != ConnectionState.Open && this._OpenedInside) this._Conn.Open();
		}


		/// <summary>
		/// 关闭连接，谁打开谁关闭
		/// </summary>
		private void CloseConnection() {
			if (this._Conn.State == ConnectionState.Open && this._OpenedInside) this._Conn.Close();
		}


		/// <summary>
		/// 解析 sql 语句，并返回格式化的 sql 语句
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		protected string ParseSQL(string sql) {
			//sql = sql.Trim();
			//Regex r1 = new Regex("^([select|insert|update|delete]) +(.+?)from +()", RegexOptions.IgnoreCase | RegexOptions.Singleline);
			//if (!r1.Match(sql)) return "";

			return "";
		}


		protected List<string> ParseUpdate() {
			return ParseUpdate(HttpContext.Current.Request.Form);
		}


		protected List<string> ParseUpdate(NameValueCollection Data) {
			StringBuilder SQL_Pairs = new StringBuilder();
			List<string> RetValue = new List<string>();
			int Start = 0;
			int End = 1;

			if (Data.Count == 0) return RetValue;

			if (!Db.TableExist(this._TableName)) {
				this.SetError(Errors.ERR_TABLE_NOT_FOUND);
				return null;
			}

			DataTable Schema = Db.TableSchema(_TableName);

			// 计算一共有多少条记录要更新
			List<string> AllData = new List<string>(Data.AllKeys);
			if (AllData.Contains("_multi_")) End = Data.GetValues("_multi_").Length;

			for (int i = Start; i < End; i++) {
				SQL_Pairs.Length = 0;

				foreach (DataRow r in Schema.Rows) {
					switch (r["COLUMN_NAME"].ToString()) {
						case "id":
							string[] TempIds = Data.GetValues(this._TableName + "." + r["COLUMN_NAME"]);
							_Id = TempIds.Length > i ? Convert.ToInt32(TempIds[i]) : 0;
							break;
						case "created":
							break;
						case "modified":
							SQL_Pairs.Append("[modified] = GETDATE(), ");
							break;
						default:
							if (AllData.Contains(this._ShortTableName + "." + r["COLUMN_NAME"].ToString())) {	//A 存在
								string[] TempArray = Data.GetValues(this._ShortTableName + "." + r["COLUMN_NAME"]);
								string TempValue = TempArray.Length > i ? TempArray[i] : "";

								SQL_Pairs.Append("[" + r["COLUMN_NAME"].ToString() + "] = ");
								if (String.IsNullOrEmpty(TempValue)) {	//提交空内容
									if (r["COLUMN_DEF"] == DBNull.Value) {	//无 default
										if (Convert.ToInt32(r["NULLABLE"]) == 0) {	//不可为null
											if (IsStringType(Convert.ToInt32(r["DATA_TYPE"]))) SQL_Pairs.Append("'', ");
											else SQL_Pairs.Append("0, ");
										} else {
											SQL_Pairs.Append("NULL, ");
										}
									} else {
										SQL_Pairs.Append("DEFAULT, ");
									}
								} else {
									SQL_Pairs.Append(GetProperValue(Convert.ToInt32(r["DATA_TYPE"]), TempValue) + ", ");
								}
							} else if (AllData.Contains(this._ShortTableName + "." + r["COLUMN_NAME"].ToString() + "_")) { //B 存在
								string[] TempArray = Data.GetValues(this._ShortTableName + "." + r["COLUMN_NAME"] + "_");
								string TempValue = TempArray.Length > i ? TempArray[i] : "";

								SQL_Pairs.Append("[" + r["COLUMN_NAME"].ToString() + "] = ");
								if (String.IsNullOrEmpty(TempValue)) {	//提交空内容
									if (r["COLUMN_DEF"] == DBNull.Value) {	//无 default
										if (Convert.ToInt32(r["NULLABLE"]) == 0) {	//不可为null
											if (IsStringType(Convert.ToInt32(r["DATA_TYPE"]))) SQL_Pairs.Append("'', ");
											else SQL_Pairs.Append("0, ");
										} else {
											SQL_Pairs.Append("NULL, ");
										}
									} else {
										SQL_Pairs.Append("DEFAULT, ");
									}
								} else {
									SQL_Pairs.Append(GetProperValue(Convert.ToInt32(r["DATA_TYPE"]), TempValue) + ", ");
								}
							}
							break;
					}
				}

				// 如果最终没有解析到可以更新的内容则返回
				if (SQL_Pairs.Length == 0) continue;

				SQL_Pairs.Remove(SQL_Pairs.Length - 2, 2);
				RetValue.Add("UPDATE [" + this._TableName + "] SET " + SQL_Pairs.ToString() + " WHERE [id] = " + _Id);
			}
			return RetValue;
		}


		/// <summary>
		/// 从 NameValueCollection 中分解 Insert sql
		/// </summary>
		/// <returns>若干条 INSERT 语句</returns>
		protected List<string> ParseInsert() {
			return ParseInsert(HttpContext.Current.Request.Form);
		}


		protected List<string> ParseInsert(NameValueCollection Data) {
			StringBuilder SQL_Field = new StringBuilder();
			StringBuilder SQL_Value = new StringBuilder();
			List<string> RetValue = new List<string>();
			DataTable Schema = new DataTable();
			int Start = 0;
			int End = 1;

			if (Data.Count == 0) return RetValue;

			// 数据库表必须存在
			if (!Db.TableExist(this._TableName)) {
				this.SetError(Errors.ERR_TABLE_NOT_FOUND);
				return null;
			}

			this.OpenConnection();
			if (this._Trans != null) Schema = Db.TableSchema(this._TableName, this._Trans);
			else Schema = Db.TableSchema(this._TableName, this._Conn);

			// 计算一共有多少条记录要插入
			List<string> AllData = new List<string>(Data.AllKeys);
			if (AllData.Contains("_multi_")) End = Data.GetValues("_multi_").Length;

			// 重复插入每一条记录
			for (int i = Start; i < End; i++) {
				SQL_Field.Length = 0;
				SQL_Value.Length = 0;

				foreach (DataRow r in Schema.Rows) {
					switch (r["COLUMN_NAME"].ToString()) {
						case "id":
							break;
						case "created":
							SQL_Field.Append("[created], ");
							SQL_Value.Append("GETDATE(), ");
							break;
						case "modified":
							SQL_Field.Append("[modified], ");
							SQL_Value.Append("GETDATE(), ");
							break;
						default:
							if (AllData.Contains(this._ShortTableName + "." + r["COLUMN_NAME"])) {	// A 存在
								string[] TempArray = Data.GetValues(this._ShortTableName + "." + r["COLUMN_NAME"]);
								string TempValue = TempArray.Length > i ? TempArray[i] : "";

								if (String.IsNullOrEmpty(TempValue)) {	//提交空内容
									if (r["COLUMN_DEF"] == DBNull.Value) {	//无 default
										if (Convert.ToInt32(r["NULLABLE"]) == 0) {	//不可为null
											SQL_Field.Append("[" + r["COLUMN_NAME"].ToString().ToLower() + "], ");
											if (IsStringType(Convert.ToInt32(r["DATA_TYPE"]))) SQL_Value.Append("'', ");
											else SQL_Value.Append("0, ");
										}	// 如果可以为 NULL 则不用显式插入
									}	// 有 default 则不用显式插入
								} else {
									SQL_Field.Append("[" + r["COLUMN_NAME"].ToString().ToLower() + "], ");
									SQL_Value.Append(GetProperValue(Convert.ToInt32(r["DATA_TYPE"]), TempValue) + ", ");
								}
							} else if (AllData.Contains(this._ShortTableName + "." + r["COLUMN_NAME"].ToString() + "_")) { //B 存在
								string[] TempArray = Data.GetValues(this._ShortTableName + "." + r["COLUMN_NAME"] + "_");
								string TempValue = TempArray.Length > i ? TempArray[i] : "";

								if (String.IsNullOrEmpty(TempValue)) {	//提交空内容
									if (r["COLUMN_DEF"] == DBNull.Value) {	//无 default
										if (Convert.ToInt32(r["NULLABLE"]) == 0) {	//不可为null
											SQL_Field.Append("[" + r["COLUMN_NAME"].ToString().ToLower() + "], ");
											if (IsStringType(Convert.ToInt32(r["DATA_TYPE"]))) SQL_Value.Append("'', ");
											else SQL_Value.Append("0, ");
										}	// 如果可以为 NULL 则不用显式插入
									}	// 有 default 则不用显式插入
								} else {
									SQL_Field.Append("[" + r["COLUMN_NAME"].ToString().ToLower() + "], ");
									SQL_Value.Append(GetProperValue(Convert.ToInt32(r["DATA_TYPE"]), TempValue) + ", ");
								}
							} else {
								if (r["COLUMN_DEF"] == DBNull.Value) {	//无 default
									if (Convert.ToInt32(r["NULLABLE"]) == 0) {	//不可为null
										SQL_Field.Append("[" + r["COLUMN_NAME"].ToString().ToLower() + "], ");
										if (IsStringType(Convert.ToInt32(r["DATA_TYPE"]))) SQL_Value.Append("'', ");
										else SQL_Value.Append("0, ");
									}	// 如果可以为 NULL 则不用显式插入
								}	// 有 default 则不用显式插入
							}
							break;
					}
				}


				// 如果最终没有解析到可以插入的内容则返回
				if (SQL_Field.Length == 0) continue;

				SQL_Field.Remove(SQL_Field.Length - 2, 2);
				SQL_Value.Remove(SQL_Value.Length - 2, 2);
				RetValue.Add("INSERT INTO [" + this._TableName + "] (" + SQL_Field.ToString() + ") VALUES (" + SQL_Value.ToString() + ")");
			}
			this.CloseConnection();
			return RetValue;
		}


		#endregion


		#region 构造函数

		public Model(string TableName) {
			this._ShortTableName = TableName;
			this._Prefix = TablePrefix();
			this._TableName = this._Prefix + TableName;
			this._ConnStr = GetConnStrInConfig();
			this._Conn = Db.GetConnection(this._ConnStr);
			this._OpenedInside = true;
		}

		public Model(string TableName, bool useTrans) {
			this._ShortTableName = TableName;
			this._Prefix = TablePrefix();
			this._TableName = this._Prefix + TableName;
			this._ConnStr = GetConnStrInConfig();
			this._Conn = Db.GetConnection(this._ConnStr);
			this._OpenedInside = true;
			if (useTrans) {
				this.OpenConnection();
				this._Trans = this._Conn.BeginTransaction();
			}
		}

		public Model(string TableName, string Prefix) {
			this._ShortTableName = TableName;
			this._Prefix = Prefix;
			this._TableName = this._Prefix + TableName;
			this._ConnStr = GetConnStrInConfig();
			this._Conn = Db.GetConnection(this._ConnStr);
			this._OpenedInside = true;
		}

		public Model(string TableName, string Prefix, bool useTrans) {
			this._ShortTableName = TableName;
			this._Prefix = Prefix;
			this._TableName = this._Prefix + TableName;
			this._ConnStr = GetConnStrInConfig();
			this._Conn = Db.GetConnection(this._ConnStr);
			this._OpenedInside = true;
			if (useTrans) {
				this.OpenConnection();
				this._Trans = this._Conn.BeginTransaction();
			}
		}

		public Model(string TableName, string Prefix, string ConnStr) {
			this._ShortTableName = TableName;
			this._Prefix = Prefix;
			this._TableName = this._Prefix + TableName;
			this._ConnStr = ConnStr;
			this._Conn = Db.GetConnection(this._ConnStr);
			this._OpenedInside = true;
		}

		public Model(string TableName, string Prefix, string ConnStr, bool useTrans) {
			this._ShortTableName = TableName;
			this._Prefix = Prefix;
			this._TableName = this._Prefix + TableName;
			this._ConnStr = ConnStr;
			this._Conn = Db.GetConnection(this._ConnStr);
			this._OpenedInside = true;
			if (useTrans) {
				this.OpenConnection();
				this._Trans = this._Conn.BeginTransaction();
			}
		}

		/// <summary>
		/// 使用外部连接
		/// </summary>
		/// <param name="TableName"></param>
		/// <param name="Conn"></param>
		public Model(string TableName, SqlConnection Conn) {
			this._ShortTableName = TableName;
			this._Prefix = TablePrefix();
			this._TableName = this._Prefix + TableName;
			this._Conn = Conn;
		}

		public Model(string TableName, string Prefix, SqlConnection Conn) {
			this._ShortTableName = TableName;
			this._Prefix = Prefix;
			this._TableName = this._Prefix + TableName;
			this._Conn = Conn;
		}

		/// <summary>
		/// 使用外部事物
		/// </summary>
		/// <param name="TableName"></param>
		/// <param name="Trans"></param>
		public Model(string TableName, SqlTransaction Trans) {
			this._ShortTableName = TableName;
			this._Prefix = TablePrefix();
			this._TableName = this._Prefix + TableName;
			this._Conn = Trans.Connection;
			this._Trans = Trans;
		}

		public Model(string TableName, string Prefix, SqlTransaction Trans) {
			this._ShortTableName = TableName;
			this._Prefix = Prefix;
			this._TableName = this._Prefix + TableName;
			this._Conn = Trans.Connection;
			this._Trans = Trans;
		}

		#endregion


		#region Insert()


		protected bool Insert() {
			return Insert(HttpContext.Current.Request.Form, false);
		}

		protected bool Insert(bool WithUpload) {
			return Insert(HttpContext.Current.Request.Form, WithUpload);
		}


		/// <summary>
		/// 插入提交数据
		/// 插入原则：
		/// 程序会检测是否存在与字段名称同名的元素;
		/// 1.	如果该元素存在并且内容非空则用其值更新字段内容，
		///		如果元素存在但内容为空则
		///			有default 值的元素更新为 default 值
		///			没有 default 值的元素
		///				可以为NULL的元素更新为NULL, 
		///				不可为NULL的, 
		///					字符串类型字段更新为空串，
		///					cursor, sql_variant, table, timestamp, uniqueidentifier 类型跳过
		///					其他类型字段更新为0;
		///	2.	如果元素不存在则，
		/// 		有default 值的元素更新为 default 值
		///			没有 default 值的元素
		///				可以为NULL的元素更新为NULL, 
		///				不可为NULL的, 
		///					字符串类型字段更新为空串，
		///					cursor, sql_variant, table, timestamp, uniqueidentifier 类型跳过
		///					其他类型字段更新为0;
		/// 
		/// 如果表单中含有名为 _multi_ 的元素程序会尝试提交多条记录
		/// </summary>
		/// <returns></returns>
		protected bool Insert(NameValueCollection Data, bool WithUpload) {
			this._Affected = 0;
			this.SetError(Errors.ERR_OK);

			NameValueCollection TempData = new NameValueCollection(Data);
			NameValueCollection TempFile = new NameValueCollection();

			if (WithUpload) {
				// 上传文件
				try {
					TempFile = Uploader.Upload();
				} catch {
					this.SetError(Errors.ERR_UPLOAD);
					return false;
				}
				if (TempFile == null) {
					SetError(Errors.ERR_UPLOAD_NOT_ALLOWED_TYPE);
					return false;
				}
				TempData.Add(TempFile);
			}

			List<string> Sqls = new List<string>();
			Sqls = ParseInsert(TempData);

			if (Sqls.Count == 0) {
				SetError(Errors.ERR_EMPTY_INPUT);
				return true;
			}

			this.OpenConnection();
			int Affected = 0;
			_Ids.Clear();

			foreach (string sql in Sqls) {
				this._Sql.Length = 0;
				this._Sql.Append(sql);

				if (this._Trans != null) {
					Affected += Db.Query(this._Sql.ToString(), this._Trans);
					this._Id = Convert.ToInt32(Db.QueryScalar("SELECT SCOPE_IDENTITY() AS ID", this._Trans));
				} else {
					Affected += Db.Query(this._Sql.ToString(), this._Conn);
					this._Id = Convert.ToInt32(Db.QueryScalar("SELECT SCOPE_IDENTITY() AS ID", this._Conn));
				}
				this._Ids.Add(_Id);
			}

			_Data.Clear();
			_Data.Add(Data);
			_Uploaded.Clear();
			_Uploaded.Add(TempFile);
			this._Affected = Affected;
			this.CloseConnection();

			return Affected > 0;
		}


		#endregion


		#region Update()


		protected bool Update() {
			return Update(HttpContext.Current.Request.Form, false);
		}

		protected bool Update(bool WithUpload) {
			return Update(HttpContext.Current.Request.Form, WithUpload);
		}


		/// <summary>
		/// 更新提交的数据
		/// 更新数据到数据库，提交的表单内必须有一个名为 table.id 的字段
		/// 
		/// 表单内没有提交的字段（A,B元素都不存在）则不用更新
		/// 
		/// 对于表单内某些没有内容或状态就不会被提交的元素-简称A元素（文件，未选取的复选框等）的替代更新策略：
		/// 如果希望A元素没有提交时使用另外的值来更新该字段则使用下面的更新策略
		/// 
		/// 首先表单内应该放置一个替代元素，简称B元素，其名称为 A元素名称 + 下划线(A元素名称_)，其中放置当A元素无法提交时的替代值。
		/// 更新原则：
		///		更新程序会检测是否存在与字段名称同名的A元素;
		///		1.	如果该A元素存在并且内容非空则用其值更新字段内容
		///			如果A元素存在但内容为空则
		///				有default 值的元素更新为 default 值
		///				没有 default 值的元素
		///					可以为NULL的元素更新为NULL, 
		///					不可为NULL的, 
		///						字符串类型字段更新为空串，
		///						cursor, sql_variant, table, timestamp, uniqueidentifier 类型跳过
		///						其他类型字段更新为0;
		///		2.	如果该A元素不存在则查找对应的B元素，如果该B元素存在并且内容非空则用其值更新字段内容，
		///			如果B元素存在但内容为空则
		///				有default 值的元素更新为 default 值
		///				没有 default 值的元素
		///					可以为NULL的元素更新为NULL, 
		///					不可为NULL的, 
		///						字符串类型字段更新为空串，
		///						cursor, sql_variant, table, timestamp, uniqueidentifier 类型跳过
		///						其他类型字段更新为0;
		///		3.	如果既没有A元素也没有B元素则放弃更新该字段
		/// 
		/// 例如有一个名称为chk的复选框元素-A元素，这时应该放置一个名为chk_的替代元素-B元素，其值为0，
		/// 这时如果提交时chk被选中则使用其值更新字段，否则使用其B元素chk_的值0来更新chk，如果B元素不存在则按照上面的第3条原则更新
		/// 
		/// 对于文件元素的更新原则与之类似，唯一不同点在于，如果有文件被提交则首先会上传文件并用返回的文件路径更新原有的字段
		/// 如果没有文件被提交则没有返回的文件路径信息从而迫使程序去寻找对应的B元素，并按照上面的更新原则进行更新。
		/// </summary>
		/// <returns></returns>
		protected bool Update(NameValueCollection Data, bool WithUpload) {
			this._Affected = 0;
			this.SetError(Errors.ERR_OK);

			NameValueCollection TempData = new NameValueCollection(Data);
			NameValueCollection TempFile = new NameValueCollection();

			if (WithUpload) {
				// 上传文件
				try {
					TempFile = Uploader.Upload();
				} catch {
					this.SetError(Errors.ERR_UPLOAD);
					return false;
				}
				if (TempFile == null) {
					SetError(Errors.ERR_UPLOAD);
					return false;
				}
				TempData.Add(TempFile);
			}

			List<string> Sqls = new List<string>();
			Sqls = ParseUpdate(TempData);

			if (Sqls.Count == 0) {
				SetError(Errors.ERR_EMPTY_INPUT);
				return true;
			}

			int Affected = 0;
			this.OpenConnection();

			foreach (string sql in Sqls) {
				this._Sql.Length = 0;
				this._Sql.Append(sql);

				if (this._Trans != null) Affected += Db.Query(this._Sql.ToString(), this._Trans);
				else Affected += Db.Query(this._Sql.ToString(), this._Conn);
			}

			_Data.Clear();
			_Data.Add(Data);
			_Uploaded.Clear();
			_Uploaded.Add(TempFile);
			this._Affected = Affected;
			this.CloseConnection();

			return Affected > 0;
		}

		#endregion



		// 自动保存post数据到数据库，至于是 INSERT 还是 UPDATE 取决于提交表单中是否有名称为 Table.id 的元素
		// 文件是否上传根据参数确定。
		// uses:	insert(NameValueCollection, bool), update(NameValueCollection, bool)

		#region Save


		public bool Save() {
			return Save(HttpContext.Current.Request.Form, false);
		}

		public bool Save(bool WithUpload) {
			return Save(HttpContext.Current.Request.Form, WithUpload);
		}


		/// <summary>
		/// 自动保存post数据到数据库，根据参数确定是否附带文件上传并记录入库
		/// </summary>
		/// <returns></returns>
		public bool Save(NameValueCollection Data, bool WithUpload) {
			List<string> data = new List<string>(Data.AllKeys);
			if (data.Contains(this._ShortTableName + "." + "id")) {
				return Update(Data, WithUpload);
			} else {
				return Insert(Data, WithUpload);
			}
		}

		#endregion









		/// <summary>
		/// 返回结果集中符合搜索条件的第一行的指定字段值
		/// </summary>
		/// <param name="Field"></param>
		/// <param name="Filter"></param>
		/// <returns></returns>

		#region Field

		public object Field(string Field, string Filter) {
			return this.Field(Field, Filter, null);
		}


		/// <summary>
		/// 返回结果集中符合搜索条件的第一行的指定字段值
		/// </summary>
		/// <param name="Field"></param>
		/// <param name="Filter"></param>
		/// <returns></returns>
		public object Field(string Field, string Filter, string Order) {
			SetError(Errors.ERR_OK);

			object ret = null;
			StringBuilder where = new StringBuilder(" WHERE 1 = 1");
			if (!String.IsNullOrEmpty(Filter)) where.Append(" AND (" + Filter + ")");

			_Sql.Length = 0;
			_Sql.Append("SELECT TOP 1 [" + Field + "] FROM [" + _TableName + "]");
			_Sql.Append(where.ToString());
			if (!String.IsNullOrEmpty(Order)) _Sql.Append(" ORDER BY " + Order);

			this.OpenConnection();
			if (this._Trans != null) ret = Db.QueryScalar(_Sql.ToString(), this._Trans);
			else ret = Db.QueryScalar(_Sql.ToString(), this._Conn);
			this.CloseConnection();
			return ret;
		}

		#endregion


		/// <summary>
		/// 返回指定id的记录
		/// </summary>
		/// <param name="Id"></param>
		/// <returns></returns>
		public DataTable Read(int Id) {
			string filter = "[id] = " + Id;
			return this.Read(filter);
		}


		/// <summary>
		/// 返回指定id的记录
		/// </summary>
		/// <param name="Id"></param>
		/// <returns></returns>
		public DataTable Read(string filter) {
			return this.Read(filter, null);
		}


		/// <summary>
		/// 返回指定id的记录
		/// </summary>
		/// <param name="Id"></param>
		/// <returns></returns>
		public DataTable Read(string filter, string order) {
			SetError(Errors.ERR_OK);

			DataTable ret = null;
			_Sql.Length = 0;
			_Sql.Append("SELECT TOP 1 * FROM [" + _TableName + "] WHERE " + filter);
			if (!String.IsNullOrEmpty(order)) _Sql.Append(" ORDER BY " + order);

			this.OpenConnection();
			if (this._Trans != null) ret = Db.QueryTable(_Sql.ToString(), this._Trans);
			else ret = Db.QueryTable(_Sql.ToString(), this._Conn);
			this.CloseConnection();
			return ret;
		}


		/// <summary>
		/// 更新指定字段
		/// 影响到 id, affected
		/// </summary>
		/// <param name="Id"></param>
		/// <returns></returns>
		public void UpdateField(string field, string value, int Id) {
			string filter = "[id] = " + Id;
			this.UpdateField(field, value, filter);
			this._Id = Id;
		}


		/// <summary>
		/// 更新指定字段
		/// 影响到 affected
		/// </summary>
		/// <param name="Id"></param>
		/// <returns></returns>
		public void UpdateField(string field, string value, string filter) {
			SetError(Errors.ERR_OK);

			StringBuilder where = new StringBuilder(" 1 = 1");
			if (!String.IsNullOrEmpty(filter)) where.Append(" AND " + filter);

			_Sql.Length = 0;
			_Sql.Append("UPDATE [" + _TableName + "] SET [" + field + "] = " + value + " WHERE " + where.ToString());

			this.OpenConnection();
			if (this._Trans != null) this._Affected = Db.Query(_Sql.ToString(), this._Trans);
			else this._Affected = Db.Query(_Sql.ToString(), this._Conn);
			this.CloseConnection();
		}


		#region Delete()

		/// <summary>
		/// 删除指定id的记录
		/// </summary>
		public int Delete(int Id) {
			return this.Delete("[id] = " + Id.ToString());
		}


		/// <summary>
		/// 删除指定条件的记录
		/// </summary>
		public int Delete(string Filter) {
			SetError(Errors.ERR_OK);

			int ret = 0;
			StringBuilder where = new StringBuilder(" WHERE 1 = 1");
			if (!String.IsNullOrEmpty(Filter)) where.Append(" AND (" + Filter + ")");

			_Sql.Length = 0;
			_Sql.Append("DELETE FROM [" + _TableName + "]");
			_Sql.Append(where.ToString());

			this.OpenConnection();
			if (this._Trans != null) ret = Db.Query(_Sql.ToString(), this._Trans);
			else ret = Db.Query(_Sql.ToString(), this._Conn);
			this._Affected = ret;
			this.CloseConnection();
			return ret;
		}

		#endregion


		/// <summary>
		/// 返回指定表指定查询条件的记录的数量
		/// </summary>
		/// <param name="Filter"></param>
		/// <returns></returns>
		public int Count(string Filter) {
			SetError(Errors.ERR_OK);

			int ret = 0;
			StringBuilder where = new StringBuilder(" WHERE 1 = 1");
			if (!String.IsNullOrEmpty(Filter)) where.Append(" AND (" + Filter + ")");

			_Sql.Length = 0;
			_Sql.Append("SELECT COUNT(8) AS Count FROM [" + _TableName + "]");
			_Sql.Append(where.ToString());

			this.OpenConnection();
			if (this._Trans != null) ret = Convert.ToInt32(Db.QueryScalar(_Sql.ToString(), this._Trans));
			else ret = Convert.ToInt32(Db.QueryScalar(_Sql.ToString(), this._Conn));
			this.CloseConnection();
			return ret;
		}


		/// <summary>
		/// 返回分页结果
		/// </summary>
		/// <param name="PageSize"></param>
		/// <param name="PageNo"></param>
		/// <param name="Filter"></param>
		/// <param name="Order"></param>
		/// <returns>返回DataTable</returns>
		public DataTable QueryPagedTable(int PageSize, int PageNo, string Filter, string Order) {
			DataTable ret = new DataTable();
			SetError(Errors.ERR_OK);
			_Sql.Length = 0;

			this.OpenConnection();
			if (this._Trans != null) ret = Db.QueryPagedTable(PageSize, PageNo, this._TableName, this._Trans, Filter, Order);
			else ret = Db.QueryPagedTable(PageSize, PageNo, this._TableName, this._Conn, Filter, Order);
			this.CloseConnection();
			return ret;
		}





		/// <summary>
		/// 返回查询数据表
		/// </summary>
		/// <param name="Field"></param>
		/// <param name="Filter"></param>
		/// <param name="Order"></param>
		/// <returns></returns>
		public DataTable QueryTable(List<string> Fields, string Filter, string Order) {
			SetError(Errors.ERR_OK);

			DataTable ret = new DataTable();
			StringBuilder Sql = new StringBuilder();
			Sql.Append("SELECT");
			if (Fields.Count == 1) {
				if (Fields[0] == "*") Sql.Append(" *, ");
				else Sql.Append(" [" + Fields[0] + "], ");
			} else {
				foreach (string field in Fields) {
					Sql.Append(" [" + field + "], ");
				}
			}
			Sql.Remove(Sql.Length - 2, 2);
			Sql.Append(" FROM [" + this._TableName + "]");
			if (!String.IsNullOrEmpty(Filter)) Sql.Append(" WHERE " + Filter);
			if (!String.IsNullOrEmpty(Order)) Sql.Append(" ORDER BY" + Order);
			_Sql.Length = 0;
			_Sql.Append(Sql.ToString());

			this.OpenConnection();
			if (this._Trans != null) ret = Db.QueryTable(_Sql.ToString(), this._Trans);
			else ret = Db.QueryTable(_Sql.ToString(), this._Conn);
			this.CloseConnection();
			return ret;
		}




		public DataTable QueryTable(string[] Fields, string Filter, string Order) {
			SetError(Errors.ERR_OK);

			DataTable ret = new DataTable();
			StringBuilder Sql = new StringBuilder();
			Sql.Append("SELECT");
			if (Fields.Length == 1) {
				if (Fields[0] == "*") Sql.Append(" *, ");
				else Sql.Append(" [" + Fields[0] + "], ");
			} else {
				foreach (string field in Fields) {
					Sql.Append(" [" + field + "], ");
				}
			}
			Sql.Remove(Sql.Length - 2, 2);
			Sql.Append(" FROM [" + this._TableName + "]");
			if (!String.IsNullOrEmpty(Filter)) Sql.Append(" WHERE " + Filter);
			if (!String.IsNullOrEmpty(Order)) Sql.Append(" ORDER BY" + Order);
			_Sql.Length = 0;
			_Sql.Append(Sql.ToString());

			this.OpenConnection();
			if (this._Trans != null) ret = Db.QueryTable(_Sql.ToString(), this._Trans);
			else ret = Db.QueryTable(_Sql.ToString(), this._Conn);
			this.CloseConnection();
			return ret;
		}




		/// <summary>
		/// 为 select 元素返回只有两列的数据表
		/// </summary>
		/// <param name="Id">值列</param>
		/// <param name="Name">文字列</param>
		/// <param name="Filter">筛选</param>
		/// <param name="Order">排序</param>
		/// <returns></returns>
		public DataTable QueryList(string Id, string Name, string Filter, string Order) {
			SetError(Errors.ERR_OK);

			DataTable ret = new DataTable();
			StringBuilder Sql = new StringBuilder();
			if (String.IsNullOrEmpty(Id)) Id = "id";
			if (String.IsNullOrEmpty(Name)) Name = "name";
			Sql.Append("SELECT [" + Id + "], [" + Name + "] FROM [" + this._TableName + "]");
			if (!String.IsNullOrEmpty(Filter)) Sql.Append(" WHERE " + Filter);
			if (!String.IsNullOrEmpty(Order)) Sql.Append(" ORDER BY" + Order);
			_Sql.Length = 0;
			_Sql.Append(Sql.ToString());

			this.OpenConnection();
			if (this._Trans != null) ret = Db.QueryTable(_Sql.ToString(), this._Trans);
			else ret = Db.QueryTable(_Sql.ToString(), this._Conn);
			this.CloseConnection();
			return ret;
		}





		#endregion



	}
}